﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;//引入命名空间
using System.Data;
using System.Data.Sql;

/// <summary>
///SQLHelper 的摘要说明
/// </summary>
public class SQLHelper
{
    private static string connstr = System.Configuration.ConfigurationManager.AppSettings["strConn"].ToString().Trim();//定义连接字符串
    public SQLHelper()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }
   
    //创建连接对象
    protected SqlConnection getConn()
    {

        SqlConnection conn = new SqlConnection(connstr);
        return conn;
    }

    //创建Command对象,执行ExecuteNonQuery()方法
    protected int ExeNonQuery(string sql)
    {
        SQLHelper hp = new SQLHelper();
        SqlConnection conn = hp.getConn();
        int result = 0;
        try
        {
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            result = (int)comm.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
        return result;

    }
    public System.Data.DataSet GetDataSet(string sqlStr)
    {
        SqlConnection myconn = getConn();
        // myconn.Open();   //打开连接
        SqlDataAdapter da = new SqlDataAdapter(sqlStr, myconn);
        DataSet ds = new DataSet();
        da.Fill(ds);
        myconn.Close();   //关闭连接
        return ds;
    }

    //创建Command对象，执行ExcuteScale（）方法
    protected int ExeScalar(string sql)
    {
        SQLHelper hp = new SQLHelper();
        SqlConnection conn = hp.getConn();
        int result = 0;
        try
        {
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            result = (int)comm.ExecuteScalar();
        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
        return result;
    }

    //创建Command对象，执行ExecuteReader()方法
    protected SqlDataReader ExeReader(string sql)
    {
        SQLHelper hp = new SQLHelper();
        SqlConnection conn = hp.getConn();
        SqlDataReader read = null;
        try
        {
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            read = comm.ExecuteReader();
        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        return read;

    }

    protected DataSet GetDs(string sql)
    {
        SQLHelper hp = new SQLHelper();
        SqlConnection conn = hp.getConn();
        DataSet ds = new DataSet ();
        try
        {
            SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
            adp.Fill(ds);
        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        return ds;
    }
}